package com.sysmanage.etl.service.impl;

import java.io.File;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sysmanage.etl.bo.SqlBO;
import com.sysmanage.etl.dao.CovertFactoryDao;
import com.sysmanage.etl.dao.ErrorLogDao;
import com.sysmanage.etl.dao.ExcelDao;
import com.sysmanage.etl.dao.RuleDao;
import com.sysmanage.etl.model.ETL_Column;
import com.sysmanage.etl.model.ETL_CovertFactory;
import com.sysmanage.etl.model.ETL_ErrorLog;
import com.sysmanage.etl.model.ETL_Excel;
import com.sysmanage.etl.model.ETL_Property;
import com.sysmanage.etl.model.ETL_Property_Column;
import com.sysmanage.etl.model.ETL_Rule;
import com.sysmanage.etl.model.ETL_Table;
import com.sysmanage.etl.service.IImportDataService;
import com.sysmanage.etl.utils.DateUtils;
import com.sysmanage.etl.utils.ETLConnectionException;
import com.sysmanage.etl.utils.ExcelUtils;
import com.sysmanage.etl.utils.JdbcUtils;
import com.sysmanage.etl.utils.SqlUtils;
import com.sysmanage.etl.utils.StringUtils;

@Service
@Transactional
public class ImportDataServiceImpl implements IImportDataService {

	@Resource
	private RuleDao ruleDao;
	@Resource
	private CovertFactoryDao covertFactoryDao;
	@Resource(name = "errorLogDao")
	private ErrorLogDao logDao;
	@Resource
	private ExcelDao excelDao;

	@Override
	public String saveData(ETL_Excel etlExcel) throws Exception {
		File excel = new File(etlExcel.getExcel_filePath());
		if (!excel.exists()) {
			return "Excel文件不存在,请重新上传!";
		}
		List<Map<Integer, String>> list = ExcelUtils.readExcelContentList(excel, etlExcel.getExcel_rowIndex());
		Integer result = null;
		try {
			result = importData(etlExcel, excel, list);
		} catch (Exception e) {
			return e.getMessage();
		}
		String feedback = "共读取" + list.size() + "条记录,其中错误" + result + "条";
		etlExcel.setResult(feedback);
		excelDao.alter(etlExcel);
		return feedback;
	}

	@Override
	public Integer saveData(ETL_Excel etlExcel, Integer... indexs) throws Exception {
		File excel = new File(etlExcel.getExcel_filePath());
		if (!excel.exists()) {
			return -99;
		}
		List<Map<Integer, String>> list = ExcelUtils.readExcelContent(excel, indexs);
		Integer result = null;
		try {
			result = importData(etlExcel, excel, list);
		} catch (Exception e) {
			return -1;
		}
		return result;
	}

	@Override
	public Integer importData(ETL_Excel etlExcel, File excel, List<Map<Integer, String>> list, Integer... indexs) throws Exception {
		Integer shopId = etlExcel.getShop_id();
		List<ETL_CovertFactory> factorys = covertFactoryDao.findList("from ETL_CovertFactory cf where cf.shop_id = " + shopId);
		

		List<ETL_Rule> rules = ruleDao.findList("from ETL_Rule r where r.shop_id = " + shopId + " order by r.rule_index asc");


		Connection connection = null;
		Map<Integer, SqlBO> indexSql = new HashMap<Integer, SqlBO>();
		// 读取excel
		List<Integer> logs = new ArrayList<Integer>();
		Integer index = null;
		for (int i = 0; i < list.size(); i++) {
			String columnName = "";
			String key = "";
			try {
				index = i;
				Map<Integer, String> map = list.get(i);
				for (ETL_Rule rule : rules) {
					Boolean isPrimary = false;
					Boolean isForgin = false;
					ETL_Table etl_Table = rule.getTable();// 获取table
					String sql = "";
					
					if (connection == null) {
						connection = JdbcUtils.getConnection(etl_Table.getDataSource(), false);
					}
					// 获取配对
					Set<ETL_Property_Column> propery_Columns = rule.getPropertyColumns();
					// 装参数和值
					Map<String, Object> params = new HashMap<String, Object>();
					Map<String, Object> repeatParams = new HashMap<String, Object>();
					ETL_Property primayKeyProperty = null;
					for (ETL_Property_Column pc : propery_Columns) {
						ETL_Property property = pc.getProperty();
						ETL_Column column = pc.getColumn();

						columnName = column != null ? column.getColumn_name() : "";
						key = property.getProperty_name();
						boolean flag = true;
						String value = null;

						// 获取主键
						if (pc.getIsPrimaryKey() != null && pc.getIsPrimaryKey()) {
							primayKeyProperty = pc.getProperty();
							/** ID生成策略 */
							if (pc.getPrimaryKey() == ETL_Property_Column.PRIMARY_GENERATE) {
								value = null;
							} else if (pc.getPrimaryKey() == ETL_Property_Column.PRIMARY_STRATEGY) {
								value = String.valueOf(System.currentTimeMillis());
							}
							flag = false;
							isPrimary = true; // 是否为主键
						}

						if ((flag || isPrimary) && pc.getIsForeignKey()) { // 处理外键
							SqlBO bo = indexSql.get(pc.getPc_index());
							value = bo.getValue();
							isForgin = true; // 是否为外键
							flag = false;
						}

						if (flag && column != null) { // 匹配Excel值
							value = map.get(column.getColumn_cellIndex());
							flag = false;
							isForgin = false;
							isPrimary = false;
						}

						if (flag && column == null) { // 其他值
							value = pc.getOtherValue();

							if (StringUtils.isNotBlank(value) && value.equals("{date}")) {
								value = DateUtils.format(new Date(), DateUtils.PARENT_ALL);
							}
							flag = false;
							isForgin = false;
							isPrimary = false;
						}

						String behindValue = null;
						for (ETL_CovertFactory factory : factorys) {
							if (factory.getProperty().equals(property)) {
								if (factory.getFactory_preValue().equals(value) || factory.getFactory_preValue().equals("{excel}")) {
									behindValue = factory.getFactory_behindValue();
									break;
								}
							}
						}

						if (StringUtils.isNotBlank(behindValue)) { // 字段纠错
							String tempValue = value;
							value = behindValue;
							if (value.startsWith("sql:")) {
								if (null == tempValue) {
									throw new Exception("Excel数据为空");
								}
								value = value.replaceAll("\\{excel\\}", tempValue);

								value = value.replaceAll("\\{date\\}", DateUtils.format(new Date(), DateUtils.PARENT_ALL));

								SqlBO tempBo = JdbcUtils.excute(new SqlBO(value.substring(4)), connection);
								value = tempBo.getValue();
								if (StringUtils.isBlank(value)) {
									throw new Exception("数据 [ "+tempValue+" ] 不能被转换");
								}

							}
						}

						if (value == null) { // 设置默认值
							value = property.getProperty_defaultValue();
						}

						if (StringUtils.isNotBlank(value) && value.equals("{date}")) {
							value = DateUtils.format(new Date(), DateUtils.PARENT_ALL);
						}

						try { // 处理数据转换失败但是字段有默认值时用默认值
							Object objValue = changeClassType(property, value);
							if (objValue != null && !(isForgin && isPrimary)) {
								params.put(key, objValue);
							}
						} catch (Exception e) {
							if (StringUtils.isNotBlank(property.getProperty_defaultValue())) {
								params.put(key, changeClassType(property, property.getProperty_defaultValue()));
							} else {
								throw new Exception("数据类型转换失败!");
							}
						}

						try {
							if (pc.getIsRepeat() || (isForgin && isPrimary)) {
								repeatParams.put(key, changeClassType(property, value));
							}
						} catch (Exception e) {
							if (StringUtils.isNotBlank(property.getProperty_defaultValue())) {
								repeatParams.put(key, changeClassType(property, property.getProperty_defaultValue()));
							} else {
								throw new Exception("数据类型转换失败!");
							}
						}
					}

					if (repeatParams.size() > 0) {
						sql = SqlUtils.repeatSql(rule, repeatParams);
						if (JdbcUtils.queryIsEmpty(sql, connection)) { // 新增
							sql = SqlUtils.createSQL(rule, ETL_Rule.TULE_TYPE_ADD, params, null);
						} else { // 修改
							sql = SqlUtils.createSQL(rule, ETL_Rule.RULE_TYPE_UPDATE, params, repeatParams);
							System.out.println("==========================================" + sql);
							JdbcUtils.excute(new SqlBO(sql), connection); // 执行更改
							System.out.println("com.jeeinc.etl>>>jdbc>>" + sql);
							ETL_Rule temp = new ETL_Rule();
							temp.setTable(rule.getTable());
							temp.setRule_return_property(primayKeyProperty);
							if (primayKeyProperty == null) {
								throw new Exception(rule.getTable().getTable_name() + "未指定主键");
							}
							sql = SqlUtils.createSQL(temp, ETL_Rule.RULE_TYPE_QUERY, repeatParams, null);
						}
					} else { // 新增
						sql = SqlUtils.createSQL(rule, rule.getRule_type(), params, null);
					}
					System.out.println("com.jeeinc.etl>>>jdbc>>" + sql);
					if (sql != null && sql.length() > 5) {
						indexSql.put(rule.getRule_index(), JdbcUtils.excute(new SqlBO(sql), connection));
					}
				}
				JdbcUtils.commit(connection); // 每行数据完成提交事务
			} catch (ETLConnectionException e1) {
				throw e1;
			} catch (Exception e) {
				ETL_ErrorLog log = new ETL_ErrorLog();
				if (indexs != null && indexs.length > 0) {
					log.setExcelColumn_Index(indexs[index]);
				} else {
					log.setExcelColumn_Index(index + etlExcel.getExcel_rowIndex() + 1);
				}
				log.setExcel(etlExcel);
				if(StringUtils.isNotBlank(columnName)){
					log.setErrorLog_cause("[" + columnName + "]" + "数值转换错误,详细 : " + e.getMessage());
				}else if(StringUtils.isNotBlank(key)){
					log.setErrorLog_cause("[" + key + "]" + "数值转换错误,详细 : " + e.getMessage());
				}
				
				logDao.save(log);

				logs.add(index);
				System.out.println("com.jeeinc.etl>>>jdbc>>封装数据时有失败");
				JdbcUtils.rollback(connection);
				e.printStackTrace();
			}
		}
		ExcelUtils.colorExcel(excel, etlExcel.getExcel_rowIndex(), logs);
		JdbcUtils.close(connection);

		if (logs.size() == 0) {
			etlExcel.setStatus(2);
		} else {
			etlExcel.setStatus(1);
		}

		excelDao.alter(etlExcel);
		return logs.size();
	}

	private Object changeClassType(ETL_Property property, String value) throws Exception {
		if (value == null || value.trim().length() <= 0 || value.equals("null")) {
			// 空值忽略处理
		} else if ("java.lang.String".equals(property.getProperty_classType())) {
			return value;
		} else if ("java.math.BigDecimal".equals(property.getProperty_classType())) {
			return BigDecimal.valueOf(Double.valueOf(value));
		} else {
			Class<?> clzz = Class.forName(property.getProperty_classType());
			Method method = clzz.getMethod("valueOf", String.class);
			System.out.println("value:" + value);
			Object newobj = method.invoke(clzz, value);
			return newobj;
		}
		return null;
	}

}
